import pymysql

'''
    封装操作数据库工具类
    将建立连接和关闭连接变成私有不让外界看到，只关注sql语句
    将操作sql语句放在类方法中，这样就不用创建类实例再调用方法，可直接用类名字.类方法
'''


class DBUtil(object):
    conn = None

    @classmethod
    def __get_conn(cls):
        if cls.conn is None:
            cls.conn = pymysql.connect(host="localhost", user="root", password="1234", database="hl")
        return cls.conn

    @classmethod
    def __close_conn(cls):
        if cls.conn:
            cls.conn.close()
            cls.conn = None

    @classmethod
    def select_one(cls, sql):
        cursor = None
        res = None
        try:
            cls.conn = cls.__get_conn()
            cursor = cls.conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as err:
            print("查询sql错误：", str(err))
        finally:
            cursor.close()
            cls.__close_conn()
            return res

    @classmethod
    def select_all(cls, sql):
        cursor = None
        res = None
        try:
            cls.conn = cls.__get_conn()
            cursor = cls.conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchall()
        except Exception as err:
            print("查询sql错误：", str(err))
        finally:
            cursor.close()
            cls.__close_conn()
            return res

    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            cls.conn = cls.__get_conn()
            cursor = cls.conn.cursor()
            cursor.execute(sql)
            print("影响的行数：", cls.conn.affected_rows())
            cls.conn.commit()

        except Exception as err:
            print("操作sql失败：", str(sql))
            cls.conn.rollback()
        finally:
            cursor.close()
            cls.__close_conn()


if __name__ == '__main__':
    res = DBUtil.select_all("select name,phone from tb_user")
    DBUtil.uid_db("update tb_user set name = 'niu' where id = 27")
    print(res)
